﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics.CodeAnalysis;
using System.Threading.Tasks;

namespace RingBufferStream.Utils.Database
{
  /// <summary>
  /// Helper class for executing SQL Server stored procedures.
  /// </summary>
  [SuppressMessage("Microsoft.Design", "CA1001:TypesThatOwnDisposableFieldsShouldBeDisposable")]
  public class SqlServerInvoke
  {
    private static readonly Random _randomWait;
    private static readonly Task _emptyTask;

    private IStoredProcedure _procedure;
    private SqlConnection _connection;
    private SqlCommand _command;
    private SqlTransaction _transaction;

    private int? _retryLimit;
    private int _retryDelay; //in Milliseconds
    private int _retryCount;

    static SqlServerInvoke()
    {
      _randomWait = new Random((int)DateTime.UtcNow.Ticks);

      var tcs = new TaskCompletionSource<int>();
      tcs.SetResult(0);
      _emptyTask = tcs.Task;
    }

    /// <summary>
    /// Number of times a stored procedure should be retried when a transient error occurs.
    /// </summary>
    public int RetryLimit
    {
      get { return (int)(_retryLimit ?? (_retryLimit = 3)); }
      set { _retryLimit = value; }
    }

    /// <summary>
    /// SQL Server connection string to use when connecting and executing a stored procedure.
    /// </summary>
    public string ConnectionString { get; set; }

    /// <summary>
    /// Procedure that will be invoked.
    /// </summary>
    public IStoredProcedure Procedure
    {
      get { return _procedure; }
      set { _procedure = value; }
    }

    /// <summary>
    /// SQL connection to the database.
    /// </summary>
    public SqlConnection Connection
    {
      get { return _connection; }
      set { _connection = value; }
    }

    /// <summary>
    /// SQL command to execute.
    /// </summary>
    public SqlCommand Command
    {
      get { return _command; }
      set { _command = value; }
    }

    /// <summary>
    /// Transaction context to use when executing the procedure.
    /// </summary>
    public SqlTransaction Transaction
    {
      get { return _transaction; }
      set { _transaction = value; }
    }

    /// <summary>
    /// Method to call to execute a stored procedure.
    /// The <see cref="ConnectionString"/> property must be set before calling this method.
    /// </summary>
    /// <remarks>
    /// The stored procedure execution is not enlisted in a transaction.
    /// Use the <see cref="ExecuteTransaction"/> method for enlisting in transaction control.
    /// </remarks>
    /// <param name="storedProcedure">The stored procedure that will be executed.</param>
    public async void ExecuteAsync(IStoredProcedure storedProcedure)
    {
      if (storedProcedure == null)
      {
        throw new ArgumentNullException("storedProcedure");
      }

      _procedure = storedProcedure;

      //Opening the connection outside the retry scope reduces SQL connection overhead when retrying
      _connection = new SqlConnection(ConnectionString);
      await ExecuteWithRetryAsync();
    }

    /// <summary>
    /// Method to call for executing a collection of stored procedures inside a transaction.
    /// The <see cref="ConnectionString"/> property must be set before calling this method.
    /// </summary>
    /// <param name="storedProcedures">The stored procedures to execute in a transaction.</param>
    public async Task ExecuteTransaction(ReadOnlyCollection<IStoredProcedure> storedProcedures)
    {
      if (storedProcedures == null)
      {
        throw new ArgumentNullException("storedProcedures");
      }
      if (storedProcedures.Count == 0)
      {
        return;
      }
      using (_connection = new SqlConnection(ConnectionString))
      {
        await ExecuteTransactionWithRetry(storedProcedures);
      }
    }

    private async Task ExecuteTransactionWithRetry(IReadOnlyCollection<IStoredProcedure> storedProcedures)
    {
      if (_connection.State == ConnectionState.Closed)
      {
        _connection.Open();
      }

      using (_transaction = _connection.BeginTransaction())
      {
        try
        {
          foreach (var procedure in storedProcedures)
          {
            _procedure = procedure;
            await ExecuteWithRetryAsync();
          }
        }
        catch
        {
          _transaction.Rollback();
          throw;
        }
        _transaction.Commit();
      }
    }

    /// <summary>
    /// Internal method for executing a stored procedure.
    /// The (protected) connection and procedure objects must be initialized.
    /// </summary>
    protected Task ExecuteWithRetryAsync()
    {
      if (_retryDelay > 0)
      {
        return Task.Delay(_retryDelay)
                   .ContinueWith
                   (
                     task =>
                     {
                       _retryDelay = 0; //Reset the delay time before retrying
                       return ExecuteWithRetryAsync();
                     },
                     TaskContinuationOptions.ExecuteSynchronously
                   ).Unwrap();
      }

      if (_connection.State == ConnectionState.Closed)
      {
        return _connection.OpenAsync()
                          .ContinueWith
                          (
                            task =>
                            {
                              if (task.Exception != null)
                              {
                                return HandleFaultedExecution(task.Exception) ? ExecuteWithRetryAsync() : _emptyTask;
                              }
                              return ExecuteCommandAsync();
                            },
                            TaskContinuationOptions.ExecuteSynchronously
                          ).Unwrap();
      }
      return ExecuteCommandAsync();
    }

    [SuppressMessage("Microsoft.Design", "CA1031:DoNotCatchGeneralExceptionTypes")]
    private Task ExecuteCommandAsync()
    {
      if (_command == null)
      {
        _command = _connection.CreateCommand();
        if (_transaction != null)
        {
          _command.Transaction = _transaction;
        }

        try
        {
          PrepareCommand();
        }
        catch (Exception exception)
        {
          FinishAndDispose(exception);
          return _emptyTask;
        }
      }
      if (!_procedure.HasResultSet)
      {
        return _command.ExecuteNonQueryAsync()
                       .ContinueWith
                       (
                         task =>
                         {
                           if (task.Exception != null)
                           {
                             return HandleFaultedExecution(task.Exception) ? ExecuteWithRetryAsync() : _emptyTask;
                           }
                           FinishAndDispose();
                           return task;
                         },
                         TaskContinuationOptions.ExecuteSynchronously
                       ).Unwrap();
      }
      return ExecuteReaderAsync();
    }

    private void FinishAndDispose(Exception exception = null)
    {
      try
      {
        if (exception == null)
        {
          _procedure.FinishRead(_command);
        }
        else
        {
          _procedure.FinishRead(exception);
        }
      }
      finally
      {
        Dispose();
      }
    }

    private Task ExecuteReaderAsync()
    {
      var commandBehavior = CommandBehavior.SingleResult | CommandBehavior.SequentialAccess;
      if (_command.Transaction == null)
      {
        commandBehavior |= CommandBehavior.CloseConnection;
      }

      return _command.ExecuteReaderAsync(commandBehavior)
                     .ContinueWith
                     (
                       task =>
                       {
                         if (task.Exception != null)
                         {
                           return HandleFaultedExecution(task.Exception) ? ExecuteWithRetryAsync() : _emptyTask;
                         }
                         _procedure.StartRead(task.Result);
                         return ReadAsync(task.Result);
                       },
                       TaskContinuationOptions.ExecuteSynchronously
                     ).Unwrap();
    }

    private Task ReadAsync(SqlDataReader reader)
    {
      return reader.ReadAsync().ContinueWith<Task>(ReadNext, reader, TaskContinuationOptions.ExecuteSynchronously).Unwrap();
    }

    [SuppressMessage("Microsoft.Design", "CA1031:DoNotCatchGeneralExceptionTypes")]
    private Task ReadNext(Task<bool> task, object state)
    {
      var reader = (SqlDataReader)state;
      if (task.Exception != null)
      {
        HandleFaultedRead(reader, task.Exception);
        return _emptyTask;
      }

      if (task.Result)
      {
        try
        {
          _procedure.ReadRecord(reader);
          return ReadAsync(reader);
        }
        catch (Exception exception)
        {
          HandleFaultedRead(reader, exception);
        }
      }
      else
      {
        reader.Dispose();
        FinishAndDispose();
      }
      return _emptyTask;
    }

    private void HandleFaultedRead(SqlDataReader reader, Exception exception)
    {
      reader.Dispose();
      FinishAndDispose(exception);
    }

    private bool HandleFaultedExecution(Exception exception)
    {
      var sqlError = exception as SqlException;
      if (sqlError != null)
      {
        ++_retryCount;
        if (HandleSqlError(sqlError))
        {
          return true;
        }
      }
      else if (HandleException(exception))
      {
        return true;
      }
      FinishAndDispose(exception);
      return false;
    }

    private void Dispose()
    {
      if (_command != null)
      {
        _command.Dispose();
      }
      if (_transaction == null)
      {
        _connection.Dispose();
      }
    }

    private bool HandleSqlError(SqlException exception)
    {
      foreach (SqlError error in exception.Errors)
      {
        if (HandleDatabaseUnavailableError(error))
        {
          return true;
        }
        if (IsTransientError(error))
        {
          return true;
        }
        if (HandleSqlError(error))
        {
          return true;
        }
      }
      return false;
    }

    private bool HandleDatabaseUnavailableError(SqlError error)
    {
      if (error.IsDatabaseUnavailable())
      {
        if (_retryCount == 1)
        {
          return true;
        }
      }
      return false;
    }

    private bool IsTransientError(SqlError error)
    {
      if (error.IsTransient())
      {
        //Close the SQL connection before sleeping, thus releasing it to the pool for re-use
        if (_connection.State == ConnectionState.Open)
        {
          if (_command != null)
          {
            _command.Dispose();
            _command = null;
          }
          _connection.Close();
        }
        if (_retryCount <= RetryLimit)
        {
          _retryDelay = _randomWait.Next(25, 101);
          return true;
        }
      }
      _retryDelay = 0;
      return false;
    }

    /// <summary>
    /// Virtual method for processing SQL errors based on the current error and retry count.
    /// </summary>
    /// <param name="sqlError">The SQL error that occurred.</param>
    /// <returns>Return true to indicate that a retry should be attempted.</returns>
    protected virtual bool HandleSqlError(SqlError sqlError)
    {
      return false;
    }

    /// <summary>
    /// Virtual method for intercepting error retries based on the current error and retry count.
    /// </summary>
    /// <param name="exception">The error that occurred.</param>
    /// <returns>Return true if a retry should be attempted.</returns>
    protected virtual bool HandleException(Exception exception)
    {
      return false; //Do not retry error!
    }

    [SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities")]
    private void PrepareCommand()
    {
      _command.CommandType = CommandType.StoredProcedure;
      _command.CommandText = _procedure.CommandText;
      OnPrepareCommand();
      _procedure.SetParameters(_command);
      _command.Prepare();
    }

    /// <summary>
    /// Virtual method for providing a hook point for a derived implementation to do additional preparation on the command.
    /// </summary>
    protected virtual void OnPrepareCommand()
    {
    }
  }
}